﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'ConsolidatedBody')
	BEGIN
		DROP  Table ConsolidatedBody
	END
GO

-- we use this table to maintain a tip version of a persons body measurements
-- for example they might take weight reguarly and measure their body fat less regularly
-- and you want to show most recent weight measurement and body fat measurement together.
-- this table holds that. Its persisted to stop potentially deep queries being run over
-- one of the key tables. Is maintained by the BodyProvider.
CREATE TABLE ConsolidatedBody
(
   AccountId int not null,
   EntryDate datetime not null,
   Weight float,
   BodyFat float,
   BMI float,
   Waist float,
   Chest float,
   Arms float,
   Thighs float,
   Height float
   
   constraint pkConsolidatedBody primary key (AccountId),
   constraint fkConsolidatedBodyToAccount foreign key (AccountId) references Account (AccountId),
   constraint ck_CWeightRange check (Weight > 0),
   constraint ck_CBodyFat check (BodyFat > 0 and BodyFat <= 100),
   constraint ck_CBMI check (BMI > 0),
   constraint ck_CWaist check (Waist > 0),
   constraint ck_CChest check (Chest > 0),
   constraint ck_CArms check (Arms > 0),
   constraint ck_CThighs check (Thighs > 0),
   constraint ck_CHeight check (Height > 0)
)
GO  